package org.zjvis.datascience.service;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.zjvis.datascience.common.constant.DatabaseConstant;
import org.zjvis.datascience.common.dto.DatasetDTO;
import org.zjvis.datascience.common.dto.DatasetsTotalInfo;
import org.zjvis.datascience.common.dto.datasetsinfo.DatasetsInfoDTO;
import org.zjvis.datascience.common.dto.user.*;
import org.zjvis.datascience.common.exception.BaseErrorCode;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.util.ConvertByteUtil;
import org.zjvis.datascience.common.util.DozerUtil;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.vo.SelectOrderVO;
import org.zjvis.datascience.common.vo.UserSelectVO;
import org.zjvis.datascience.common.vo.notice.FeedbackNoticeAddVO;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;
import org.zjvis.datascience.service.mapper.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

/**
 * @description 管理员调用接口，查询到所有用户信息，对某用户查询其表信息
 * @date 2021-10-18
 */
@Service
public class AdminService {
    private final static Logger logger = LoggerFactory.getLogger("AdminService");

    @Autowired
    private UserInfoMapper userInfoMapper;

    @Autowired
    private UserFeedbackMapper userFeedbackMapper;

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private DatasetMapper datasetMapper;

    @Autowired
    private DatasetsInfoMapper datasetsInfoMapper;

    @Autowired
    private UserMapper userMapper;


    /**
     * 管理员查看选定用户反馈
     */
    public UserFeedbackDTO queryById(Long id) {
        return userFeedbackMapper.selectByPrimaryKey(id);
    }

    /**
     * 更新用户的管理员权限
     * @param user
     */
    public boolean updateUserRole(UserDTO user) {
        UserDTO dto = JwtUtil.getCurrentUserDTO();
        if (user.getName().equals(dto.getName())){
            throw new DataScienceException(BaseErrorCode.ADMIN_CANNOT_UPDATE);
        }
        List<UserDTO> result = userMapper.select(user);
        if (result == null || result.isEmpty()){
            throw new DataScienceException(BaseErrorCode.USER_USERNAME_OR_PASSWORD_ERROR);
        }
        //创建新user, 避免被user里的参数强行修改用户信息
        UserDTO new_user = new UserDTO();
        new_user.setRole(user.getRole());
        new_user.setId(result.get(0).getId());
        userMapper.updateByPrimaryKeySelective(new_user);
        return true;
    }

    /**
     * 管理员筛选用户反馈
     * @param dto
     * @return
     */
    public PageInfo<UserFeedbackDTO> selectFeedbacks(QueryFeedbackDTO dto) {
        if(dto.getPageSize() == null || dto.getPageSize() == 0) {
            dto.setPageSize(15); //默认每页显示15条记录
        }
        if(dto.getCurPage() == null || dto.getCurPage() <= 0) {
            dto.setCurPage(1); //默认显示第一页
        }
        dto.setPageStart((dto.getCurPage()-1) * dto.getPageSize());
        PageHelper.startPage(dto.getCurPage(), dto.getPageSize());
        List<UserFeedbackDTO> feedbackDTOS = userFeedbackMapper.selectFeedbacks(dto);
        return new PageInfo<>(feedbackDTOS);
    }


    /**
     * 管理员查看所有用户信息：表的数量、总文件大小、userid、user名
     */
    public JSONObject selectAllUserInfo(UserSelectVO userSelectVO) {
        List<SelectOrderVO> selectOrderVOList = userSelectVO.getSelectOrderVOList();
        String orderRule = "";
        if (selectOrderVOList != null) {
            boolean flag = false;
            for (SelectOrderVO selectOrderVO : selectOrderVOList) {
                if (!flag) {
                    flag = true;
                } else {
                    orderRule += ", ";
                }
                orderRule += selectOrderVO.getField() + " " + selectOrderVO.getOrderRule() + " ";
            }
        }
        Page page = PageHelper.startPage(userSelectVO.getCurPage(), userSelectVO.getPageSize(), orderRule);
        List<UserInfoDTO> userDTOList = userInfoMapper.selectAllUserInfo(userSelectVO);
        PageInfo pageInfo = new PageInfo<>(page.getResult());

        JSONObject result = new JSONObject();
        result.put("code", 100);

        if (userDTOList.size() == 0) {
            result.put("code", 401);
            result.put("errMsg", "用户数据不存在");
            return result;
        }

        for (UserInfoDTO userInfoDTO : userDTOList) {
            if (userInfoDTO.getDatasetSizeLong() != null)
                userInfoDTO.setDatasetSize(ConvertByteUtil.convertBytes(userInfoDTO.getDatasetSizeLong()));
        }
        result.put("pageNum", pageInfo.getPages());
        result.put("data", userDTOList);
        return result;
    }

    /**
     * 管理员查看所有用户信息：表的数量、总文件大小、userid、user名
     */
    public JSONObject selectAllUserDataSetInfo(UserSelectVO userSelectVO) {
        List<SelectOrderVO> selectOrderVOList = userSelectVO.getSelectOrderVOList();
        String orderRule = "";
        if (selectOrderVOList != null) {
            boolean flag = false;
            for (SelectOrderVO selectOrderVO : selectOrderVOList) {
                if (!flag) {
                    flag = true;
                } else {
                    orderRule += ", ";
                }
                // todo : field 需要转化
                orderRule += selectOrderVO.getField() + " " + selectOrderVO.getOrderRule() + " ";
            }
        }
        Page page = PageHelper.startPage(userSelectVO.getCurPage(), userSelectVO.getPageSize(), orderRule);
        List<UserDataInfoDTO> userDataInfoDTOS = userInfoMapper.selectAllUserDatasetInfo(userSelectVO);
        PageInfo pageInfo = new PageInfo<>(page.getResult());
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;

        JSONObject result = new JSONObject();
        result.put("code", 100);

        if (userDataInfoDTOS.size() == 0) {
            result.put("code", 401);
            result.put("errMsg", "用户数据集不存在");
            return result;
        }

        try {
            //获取gp数据库中数据集库的连接（指定id为1）
            con = gpDataProvider.getConn(DatabaseConstant.GREEN_PLUM_DATASET_ID);
        } catch (Exception e) {
            result.put("code", 500);
            result.put("errMsg", "连接错误");
            logger.error("admin_selectAllUserDataSetInfo_errMsg", e);
            JDBCUtil.close(con, null, null);
            return result;
        }
        // 根据用户id搜索dataset，得到表数量，然后找到每个dataset在gp的位置，从gp获取大小
        for (UserDataInfoDTO userDataInfoDTO : userDataInfoDTOS) {
            Long size;
            if ((size = userDataInfoDTO.getDatasetSize()) == null || userDataInfoDTO.getUpdate() != null) {
                String sizeString = "0 bytes";
                DatasetDTO dataset = datasetMapper.queryById(userDataInfoDTO.getDatasetId());
                JSONObject config = JSONObject.parseObject(dataset.getDataJson());
                String table = config.getString("table");
                String schema = config.getString("schema");
                try {
                    if (config.get("update") != null) config.remove("update");
                    /* 获取数据总条数(表加索引总大小)，表大小用:pg_table_size,索引大小用：pg_indexes_size */
                    ps = con.prepareStatement(String.format(DatabaseConstant.GP_SELECT_TOTAL_RELATION_SIZE,schema, SqlUtil.formatPGSqlColName(table)));
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        sizeString = rs.getString(1);
                    }
                    size = ConvertByteUtil.convertLong(sizeString);
                    config.put("size", size);
                    dataset.setDataJson(config.toJSONString());
                    datasetMapper.update(dataset);
                }  catch (Exception e) {
//                        result.put("code", 500);
//                        result.put("errMsg", "连接错误");
                    logger.error("admin_selectAllUserDataSetInfo_errMsg", e);
                    JDBCUtil.close(null, ps, rs);
                    continue;
                }
            }
            JDBCUtil.close(null, ps, rs);
            userDataInfoDTO.setDatasetSize(size);
            userDataInfoDTO.setDatasetSizeString(ConvertByteUtil.convertBytes(size));
        }
        JDBCUtil.close(con, null, null);
        result.put("pageNum", pageInfo.getPages());
        result.put("data", userDataInfoDTOS);
        return result;
    }

    public List<String> selectLikeDataName(String datasetName) {
        return datasetMapper.selectLikeDataName(datasetName);
    }

    /**
     * 管理员查看特定用户的表信息，包括：表名、表大小、行数、新增变化
     */
    public JSONObject queryDatasetInfoByUserId(Long userId){
        // 得到指定用户的表信息
        List<DatasetsInfoDTO> datasetsInfoDTOS = datasetsInfoMapper.selectBy(userId, new DatasetsInfoDTO());
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        // 所有表的数量
        Long tableCnt = (long) datasetsInfoDTOS.size();
        // 所有表的size
        Long tableSize = 0L;

        JSONObject result = new JSONObject();
        result.put("code", 100);

        try {
            //获取gp数据库中数据集库的连接（指定id为1）
            con = gpDataProvider.getConn(DatabaseConstant.GREEN_PLUM_DATASET_ID);
        } catch (Exception e) {
            result.put("code", 500);
            result.put("errMsg", "连接错误");
            logger.error("admin_queryDatasetInfoByUserId_errMsg"+ e.getMessage());
            JDBCUtil.close(con, null, null);
            return result;
        }

        for (DatasetsInfoDTO datasetsInfoDTO : datasetsInfoDTOS) {
            Long size = datasetsInfoDTO.getTotalSize();
            if (size == null || datasetsInfoDTO.getUpdate() != null ) {
                // 若size未知找到每个dataset在gp的位置，从gp获取大小
                DatasetDTO dataset = datasetMapper.queryById(datasetsInfoDTO.getId());
                JSONObject config = JSONObject.parseObject(dataset.getDataJson());
                String table = config.getString("table");
                String schema = config.getString("schema");
                size = 0L;
                String sizeString = "0 bytes";
                try {
                    if (config.get("update") != null) config.remove("update");
                    /* 获取数据总条数(表加索引总大小)，表大小用:pg_table_size,索引大小用：pg_indexes_size */
                    ps = con.prepareStatement(String.format(DatabaseConstant.GP_SELECT_TOTAL_RELATION_SIZE,schema, SqlUtil.formatPGSqlColName(table)));
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        sizeString = rs.getString(1);
                    }
                    size = ConvertByteUtil.convertLong(sizeString);
                    config.put("size", size);
                    dataset.setDataJson(config.toJSONString());
                    datasetMapper.update(dataset);
                } catch (Exception e) {
                    logger.error("admin_queryDatasetInfoByUserId_errMsg"+ e.getMessage());
                    tableCnt--;
                }
            }
            datasetsInfoDTO.setTotalSize(size);
            datasetsInfoDTO.setTotalSizeString(ConvertByteUtil.convertBytes(size));
            tableSize += size;
        }

        JDBCUtil.close(con, ps, rs);
        result.put("data", DatasetsTotalInfo
                .builder()
                .datasetsInfoDTOList(datasetsInfoDTOS)
                .totalSize(ConvertByteUtil.convertBytes(tableSize))
                .tableCnt(tableCnt).build());
        return result;
    }

    public int updateFeedbackStatus(FeedbackNoticeAddVO vo) {
        UserFeedbackDTO feedback = DozerUtil.mapper(vo,UserFeedbackDTO.class);
        feedback.setStatus(1);
        return userFeedbackMapper.updateUserFeedback(feedback);
    }
}
